package cn.com.open.itembank.sqlprovider;

import java.util.Map;

public class AppSqlProvider {
    /**
     * 根据APPID获取对应题库试题数量信息SQL
     *
     * @param parameter 参数列表
     * @return 对应题库试题数量信息SQL
     */
    public String getItemBankQuestionCountByAppKey(Map<String, Object> parameter) {
        String appKey = (String) parameter.get("appKey");

        StringBuilder strBuilder = new StringBuilder();
        strBuilder.append("select c.ItemBankID,c.CourseName,isnull(CNT,0) as Count ");
        strBuilder.append("from CourseItemBank as c ");
        strBuilder.append(" left join( ");
        strBuilder.append(" select re.ItemBankID, sum(1) as CNT ");
        strBuilder.append(" from QuestionUseRelation as re ");
        strBuilder.append(" join Question as q on q.QuestionTypeID=re.QuestionID ");
        strBuilder.append(" where q.IsCheck=1 and q.Status=0 ");
        strBuilder.append(" group by re.ItemBankID ");
        strBuilder.append(" ) CNT on CNT.ItemBankID=c.ItemBankID ");
        strBuilder.append(String.format("where c.AppID='%s' and c.Status=0 ", appKey));
        strBuilder.append("order by Count ");

        return strBuilder.toString();
    }
}
